import 'package:dartkit/dartkit.dart';
import 'package:postgres/postgres.dart';

part "model.dart";

class UserTable extends TableGeneral<User> {
  @override
  final String tableName = "users";

  @override
  final Connection conn;

  @override
  User unzip(Json raw) => User.fromRaw(raw);

  UserTable(this.conn) {
    assert(conn.isOpen);
  }

  @override
  Future<void> createTable() async {
    // 手机号需要符合11位长度，并且以1开头
    await conn.execute("""
      CREATE TABLE IF NOT EXISTS $tableName (
        id SERIAL,
        nick VARCHAR(20) NOT NULL DEFAULT '',
        is_admin BOOLEAN NOT NULL DEFAULT FALSE,
        phone VARCHAR(11) CHECK (phone ~ '^1\\d{10}\$'),
        sms JSONB,
        PRIMARY KEY (id),
        UNIQUE (phone)
      );
""");

    // 在 phone 列上创建索引
    await conn.execute("""
      CREATE INDEX IF NOT EXISTS idx_${tableName}_phone ON $tableName (phone);
    """);
  }

  /// 在第一个参数中输入手机号即可
  @override
  Future<User> create(Json args) async {
    final String phone = args.getField<String>("phone", "需要手机号来创建用户");

    final result = await conn.execute(Sql.named("""
      INSERT INTO $tableName (phone)
      VALUES (@phone)
      RETURNING *;
  """), parameters: {
      'phone': phone,
    });

    final row = result.first;
    return User.fromRaw(row.toColumnMap());
  }

  @override
  Future<void> update(User item) async {
    await conn.execute(Sql.named("""
      UPDATE $tableName
      SET nick = @nick, is_admin = @is_admin, phone = @phone, sms = @sms
      WHERE id = @id;
  """), parameters: {
      'id': item.id,
      'nick': item.nick,
      'is_admin': item.isAdmin,
      'phone': item.phone,
      'sms': item.sms?.toJson(), // 将 Sms 对象转换为 JsonMap
    });
  }
}

extension UserTableExtraSql on UserTable {
  /// 在未登录的时候，实际上需要根据手机号来查找用户
  Future<User?> findByPhone(String phone) async {
    final results = await conn.execute(Sql.named("""
    SELECT *
    FROM $tableName
    WHERE phone = @phone;
  """), parameters: {
      'phone': phone,
    });

    if (results.isEmpty) return null;

    final row = results.first;
    return User.fromRaw(row.toColumnMap());
  }

  Future<User> findOrCreate(String phone) async {
    // 先尝试通过手机号查找用户，如果找到，则直接返回
    final User? user = await findByPhone(phone);
    if (user != null) return user;
    // 如果找不到，则创建一个新用户，并返回这个新的用户
    final User newUser = await create({"phone": phone});
    return newUser;
  }
}

final UserTable userTable = UserTable(argConn.value);
